package com.eystar.ext.plugin.crate.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import com.jfinal.log.Log4jLog;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

public class CrateDbPro {
	
	public static final Log4jLog logger = Log4jLog.getLog(CrateDbPro.class);
	
	static final Object[] NULL_PARA_ARRAY = new Object[0];

	public <T> List<T> query(String sql) {
		return query(sql, NULL_PARA_ARRAY);
	}
	
	public <T> List<T> query(String sql, Object... paras) {
		Connection conn = null;
		try {
			conn = CratePool.getConnection();
			return query(conn, sql, paras);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			closeConn(conn);
		}
	}
	
	public <T> T queryFirst(String sql) {
		return queryFirst(sql, NULL_PARA_ARRAY);
	}
	
	public <T> T queryFirst(String sql, Object... paras) {
		List<T> result = query(sql, paras);
		return (result.size() > 0 ? result.get(0) : null);
	}
	
	public List<Record> find(String sql) {
		return find(sql, NULL_PARA_ARRAY);
	}
	
	public List<Record> find(String sql, Object... paras) {
		Connection conn = null;
		try {
			conn = CratePool.getConnection();
			return find(conn, sql, paras);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			closeConn(conn);
		}
	}
	
	public Record findFirst(String sql) {
		return findFirst(sql, NULL_PARA_ARRAY);
	}
	
	public Record findFirst(String sql, Object... paras) {
		List<Record> result = find(sql, paras);
		return result.size() > 0 ? result.get(0) : null;
	}
	
	public int update(String sql) {
		return update(sql, NULL_PARA_ARRAY);
	}
	
	public int update(String sql, Object... paras) {
		Connection conn = null;
		try {
			conn = CratePool.getConnection();
			return update(conn, sql, paras);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			closeConn(conn);
		}
	}
	
	public Page<Record> paginate(int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... paras) {
		Connection conn = null;
		try {
			conn = CratePool.getConnection();
			return paginate(conn, pageNumber, pageSize, select, sqlExceptSelect, paras);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			closeConn(conn);
		}
	}
	
	Page<Record> paginate(Connection conn, int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... paras) throws SQLException {
		return doPaginate(conn, pageNumber, pageSize, null, select, sqlExceptSelect, paras);
	}
	
	Page<Record> doPaginate(Connection conn, int pageNumber, int pageSize, Boolean isGroupBySql, String select, String sqlExceptSelect, Object... paras) throws SQLException {
		if (pageNumber < 1 || pageSize < 1) {
			throw new RuntimeException("pageNumber and pageSize must more than 0");
		}
		
		String totalRowSql = "select count(*) " + CrateKit.replaceOrderBy(sqlExceptSelect);
		List<?> result = query(conn, totalRowSql, paras);
		int size = result.size();
		if (isGroupBySql == null) {
			isGroupBySql = size > 1;
		}
		
		long totalRow;
		if (isGroupBySql) {
			totalRow = size;
		} else {
			totalRow = (size > 0) ? ((Number)result.get(0)).longValue() : 0;
		}
		if (totalRow == 0) {
			return new Page<Record>(new ArrayList<Record>(0), pageNumber, pageSize, 0, 0);
		}
		
		int totalPage = (int) (totalRow / pageSize);
		if (totalRow % pageSize != 0) {
			totalPage++;
		}
		
		if (pageNumber > totalPage) {
			return new Page<Record>(new ArrayList<Record>(0), pageNumber, pageSize, totalPage, (int)totalRow);
		}
		
		// --------
		String sql = CrateKit.forPaginate(pageNumber, pageSize, select, sqlExceptSelect);
		List<Record> list = find(conn, sql, paras);
		return new Page<Record>(list, pageNumber, pageSize, totalPage, (int)totalRow);
	}
	

	List<Record> find(Connection conn, String sql, Object... paras) throws SQLException {
		PreparedStatement pst = conn.prepareStatement(sql);
		if (paras != null && paras.length > 0) {
			for (int i=0; i<paras.length; i++) {
				pst.setObject(i + 1, paras[i]);
			}
		}
		ResultSet rs = pst.executeQuery();
		List<Record> result = CrateRecordBuilder.build(rs);
		closeRsPst(rs, pst);
		return result;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	<T> List<T> query(Connection conn, String sql, Object... paras) throws SQLException {
		List result = new ArrayList();
		PreparedStatement pst = conn.prepareStatement(sql);
		if (paras != null && paras.length > 0) {
			for (int i=0; i<paras.length; i++) {
				pst.setObject(i + 1, paras[i]);
			}
		}
		ResultSet rs = pst.executeQuery();
		int colAmount = rs.getMetaData().getColumnCount();
		if (colAmount > 1) {
			while (rs.next()) {
				Object[] temp = new Object[colAmount];
				for (int i=0; i<colAmount; i++) {
					temp[i] = rs.getObject(i + 1);
				}
				result.add(temp);
			}
		}else if(colAmount == 1) {
			while (rs.next()) {
				result.add(rs.getObject(1));
			}
		}
		closeRsPst(rs, pst);
		return result;
	}
	public List<String> forDbSave(String tableName, Record record, StringBuilder sql, List<Object> paras) {
		tableName = tableName.trim();
		List<String> columns = new ArrayList<>();
		sql.append("insert into \"");
		sql.append(tableName).append("\"(");
		StringBuilder temp = new StringBuilder();
		temp.append(") values(");
		for (Entry<String, Object> e: record.getColumns().entrySet()) {
			if (paras.size() > 0) {
				sql.append(", ");
				temp.append(", ");
			}
			sql.append("\"").append(e.getKey()).append("\"");
			temp.append("?");
			paras.add(e.getValue());
			columns.add(e.getKey());
		}
		sql.append(temp.toString()).append(")");
		return columns;
	}
	
	public List<String> forDbUpdate(String tableName,  Record record, StringBuilder sql, List<Object> paras) {
		List<String> columns = new ArrayList<>();
		sql.append("update ").append(tableName).append(" set ");
		Object idValue = null;
		for (Entry<String, Object> e: record.getColumns().entrySet()) {
			String colName = e.getKey();
			if(colName.equals("id")){
				idValue = e.getValue();
				continue;
			}
			if (paras.size() > 0) {
				sql.append(", ");
			}
			sql.append("\"").append(colName).append("\" = ? ");
			paras.add(e.getValue());
			columns.add(e.getKey());
		}
		sql.append(" where ");
		sql.append("\"id\" = ?");
		paras.add(idValue);
		columns.add("id");
		return columns;
	}
	
	int update(Connection conn, String sql, Object... paras) throws SQLException {
		PreparedStatement pst = conn.prepareStatement(sql);
		if (paras != null && paras.length > 0) {
			for (int i=0; i<paras.length; i++) {
				pst.setObject(i + 1, paras[i]);
			}
		}
		int result = pst.executeUpdate();
		closeRs(pst);
		return result;
	}
	public int[] batchSave(String tableName, List<Record> recordList, int batchSize) {
    	if (recordList == null || recordList.size() == 0)
    		return new int[0];
    	
    	Record record = recordList.get(0);
    	Map<String, Object> cols = record.getColumns();
    	if(!cols.containsKey("id")) cols.put("id", "");
    	StringBuilder sql = new StringBuilder();
    	List<Object> parasNoUse = new ArrayList<Object>();
    	List<String> columns = forDbSave(tableName, record, sql, parasNoUse);
    	Connection conn = null;
    	int[] result = null;
		try {
			conn = CratePool.getConnection();
			result = batch(conn, sql.toString(), columns, recordList, batchSize);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			closeConn(conn);
		}
		return result;
    }
	public int[] batchUpdate(String tableName, List<Record> recordList, int batchSize) {
		if (recordList == null || recordList.size() == 0)
			return new int[0];
		
		Record record = recordList.get(0);
		Map<String, Object> cols = record.getColumns();
		if(!cols.containsKey("id")){
			throw new IllegalArgumentException("The update Record must have id colum.");
		}
		StringBuilder sql = new StringBuilder();
		List<Object> parasNoUse = new ArrayList<Object>();
		List<String> columns = forDbUpdate(tableName, record, sql, parasNoUse);
		Connection conn = null;
		int[] result = null;
		try {
			conn = CratePool.getConnection();
			result = batch(conn, sql.toString(), columns, recordList, batchSize);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			closeConn(conn);
		}
		return result;
	}
	
	
	private int[] batch(Connection conn, String sql, List<String> columns, List<Record> list, int batchSize) throws SQLException {
		if (list == null || list.size() == 0)
			return new int[0];
		Object element = list.get(0);
		if (!(element instanceof Record) && !(element instanceof Model))
			throw new IllegalArgumentException("The element in list must be Model or Record.");
		if (batchSize < 1)
			throw new IllegalArgumentException("The batchSize must more than 0.");
		
		int counter = 0;
		int pointer = 0;
		int size = list.size();
		int[] result = new int[size];
		PreparedStatement pst = conn.prepareStatement(sql);
		conn.setAutoCommit(false);//将自动提交关闭
		for (int i=0; i<size; i++) {
			Map<?, ?> map = ((Record)list.get(i)).getColumns();
			for (int j=0; j<columns.size(); j++) {
				Object value = map.get(columns.get(j));
				pst.setObject(j + 1, value);
			}
			pst.addBatch();
			if (++counter >= batchSize) {
				counter = 0;
				try {
					int[] r = pst.executeBatch();
					//conn.commit();
					for (int k=0; k<r.length; k++)
						result[pointer++] = r[k];
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
			}
		}
		try {
			int[] r = pst.executeBatch();
			//conn.commit();
			for (int k=0; k<r.length; k++)
				result[pointer++] = r[k];
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
		conn.commit();
		closeRs(pst);
		return result;
	}
	
	public int[] batch(List<String> sqlList, int batchSize) {
    	if (sqlList == null || sqlList.size() == 0)
    		return new int[0];
    	Connection conn = null;
    	int[] result = null;
		try {
			conn = CratePool.getConnection();
			result = batch(conn, sqlList, batchSize);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			closeConn(conn);
		}
		return result;
    }
	
	private int[] batch(Connection conn, List<String> sqlList, int batchSize) throws SQLException {
		if (sqlList == null || sqlList.size() == 0)
			return new int[0];
		if (batchSize < 1)
			throw new IllegalArgumentException("The batchSize must more than 0.");
		
		int counter = 0;
		int pointer = 0;
		int size = sqlList.size();
		int[] result = new int[size];
		Statement st = conn.createStatement();
		for (int i=0; i<size; i++) {
			st.addBatch(sqlList.get(i));
			if (++counter >= batchSize) {
				counter = 0;
				int[] r = st.executeBatch();
				conn.commit();
				for (int k=0; k<r.length; k++)
					result[pointer++] = r[k];
			}
		}
		int[] r = st.executeBatch();
		conn.commit();
		for (int k=0; k<r.length; k++)
			result[pointer++] = r[k];
		closeRs(st);
		return result;
	}
	
	final void closeRs(Statement st) {
		if (st != null) {try {st.close();} catch (SQLException e) {throw new RuntimeException(e);}}
	}
	
	final void closeRsPst(ResultSet rs, Statement st) {
		if (rs != null) {try {rs.close();} catch (SQLException e) {throw new RuntimeException(e);}}
		if (st != null) {try {st.close();} catch (SQLException e) {throw new RuntimeException(e);}}
	}
	
	final void closeConn(Connection conn) {
		CratePool.returnObject(conn);
	}
	
	
	public int[] batchSavePassError(String tableName, List<Record> recordList, int batchSize) {
    	if (recordList == null || recordList.size() == 0)
    		return new int[0];
    	
    	Record record = recordList.get(0);
    	StringBuilder sql = new StringBuilder();
    	List<Object> parasNoUse = new ArrayList<Object>();
    	List<String> columns = forDbSave(tableName, record, sql, parasNoUse);
    	Connection conn = null;
    	int[] result = null;
		try {
			conn = CratePool.getConnection();
			result = batchPassError(conn, sql.toString(), columns, recordList, batchSize);
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			closeConn(conn);
		}
		return result;
    }
	
	private int[] batchPassError(Connection conn, String sql, List<String> columns, List<Record> list, int batchSize) throws SQLException {
		if (list == null || list.size() == 0)
			return new int[0];
		Object element = list.get(0);
		if (!(element instanceof Record) && !(element instanceof Model))
			throw new IllegalArgumentException("The element in list must be Model or Record.");
		if (batchSize < 1)
			throw new IllegalArgumentException("The batchSize must more than 0.");
		
		int counter = 0;
		int pointer = 0;
		int size = list.size();
		int[] result = new int[size];
		PreparedStatement pst = conn.prepareStatement(sql);
		for (int i=0; i<size; i++) {
			Map<?, ?> map = ((Record)list.get(i)).getColumns();
			for (int j=0; j<columns.size(); j++) {
				Object value = map.get(columns.get(j));
				pst.setObject(j + 1, value);
			}
			pst.addBatch();
			if (++counter >= batchSize) {
				counter = 0;
				try {
					int[] r = pst.executeBatch();
					//conn.commit();
					for (int k=0; k<r.length; k++)
						result[pointer++] = r[k];
				} catch (Exception e) {
					//e.printStackTrace();
				}
				
			}
		}
		try {
			int[] r = pst.executeBatch();
			//conn.commit();
			for (int k=0; k<r.length; k++)
				result[pointer++] = r[k];
		} catch (Exception e) {
			//e.printStackTrace();
		}
		closeRs(pst);
		return result;
	}
}
    
